package com.budwk.app.sqfw.controllers.admin;

import cn.dev33.satoken.annotation.SaCheckLogin;
import cn.hutool.core.util.NumberUtil;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.openapi.annotation.ApiDefinition;
import com.budwk.starter.common.result.Result;
import lombok.extern.slf4j.Slf4j;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.util.NutMap;
import org.nutz.mvc.annotation.At;
import org.nutz.mvc.annotation.Ok;
import org.nutz.mvc.annotation.POST;
import org.nutz.mvc.annotation.Param;

import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;

@IocBean
@At("/platform/sqfw/largeScreen/")
@ApiDefinition(tag = "大屏")
@Slf4j
public class LargeScreenController {
    @Inject
    private SqfwFormsummaryNewService sqfwFormsummaryNewService;
    @At
    @Ok("json")
    @POST
    @SaCheckLogin
    public Result<?> guanshan(@Param("startDate") String startDate, @Param("endDate") String endDate) {


        Dao dao =  NutzIocUtils.getBean(Dao.class);
        //店铺
        Sql shopSql = Sqls.fetchInt("select count(*) from shequ.sqfw_form_summary_new sfs , shequ.sqfw_shops_base_info ssbi where " +
                "sfs.county = '观山街道' and sfs.type = '安全排查整治' and sfs.shops_base_info_id = ssbi.id " +
                "and ssbi.type in('九小场所', '多业态混合经营场所', '社会治安场所的数量', '大型企业', '企业安全生成主体') " +
                "and sfs.fill_date between  @startDate and  @endDate");
        shopSql.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(shopSql);
        //房屋
        Sql tenementSql = Sqls.fetchInt("select count(*) from shequ.sqfw_form_summary_new sfs , shequ.sqfw_shops_base_info ssbi where" +
                " sfs.county = '观山街道' and sfs.type = '安全排查整治' and sfs.shops_base_info_id = ssbi.id" +
                " and ssbi.type in('重点场所','人员密集场所','大型企业','企业安全生成主体','住宅楼栋','建筑工地','楼宇') " +
                " and sfs.fill_date between  @startDate and  @endDate");
        tenementSql.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(tenementSql);
        //法人
        Sql legalPersonSql = Sqls.fetchInt("select count(*) from shequ.sqfw_form_summary_new sfs , shequ.sqfw_shops_base_info ssbi where" +
                " sfs.county = '观山街道' and sfs.type = '安全排查整治' and sfs.shops_base_info_id = ssbi.id" +
                " and ssbi.type in('重点场所','人员密集场所','多业态混合经营场所','大型企业','企业安全生成主体','九小场所') " +
                " and sfs.fill_date between  @startDate and  @endDate");
        legalPersonSql.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(legalPersonSql);
        //消防
        Sql fireFightingSql =Sqls.fetchInt("select count(*) from shequ.sqfw_form_summary_new sfs , shequ.sqfw_shops_base_info ssbi where" +
                " sfs.county = '观山街道' and sfs.type = '安全排查整治' and sfs.shops_base_info_id = ssbi.id " +
                " and sfs.fill_date between  @startDate and  @endDate");
        fireFightingSql.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(fireFightingSql);
        //食品
        Sql foodSql = Sqls.fetchInt("select count(*) from shequ.sqfw_form_summary_new sfs , shequ.sqfw_shops_base_info ssbi where" +
                " sfs.county = '观山街道' and sfs.type = '安全排查整治' and sfs.shops_base_info_id = ssbi.id " +
                " and param_data ->>'type' = '九小场所' and param_data ->>'placeType' in ('店铺','餐饮','销售') " +
                " and sfs.fill_date between  @startDate and  @endDate");
        foodSql.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(foodSql);


        //line1_column2
        Integer line1_column2 =sqfwFormsummaryNewService.count(Cnd.where(Sqfw_formSummary_new::getType,"=","平安建设宣传")
                .and(Sqfw_formSummary_new::getCounty,"=","观山街道")
                .and(Sqfw_formSummary_new::getFillDate,"between",new Object[]{startDate, endDate}));
        //line1_column3
        Integer line1_column3 =sqfwFormsummaryNewService.count(Cnd.where(Sqfw_formSummary_new::getType,"in", Arrays.asList("安全排查整治","社会治安"))
                .and(Sqfw_formSummary_new::getCounty,"=","观山街道")
                .and(Sqfw_formSummary_new::getFillDate,"between",new Object[]{startDate, endDate}));


        //line1_column4
        Sql line1_column4 = Sqls.fetchInt("select count(*) from (select community from shequ.sqfw_form_summary_new where type in ('安全排查整治','社会治安')  and county = '观山街道' " +
                " and fill_date between  @startDate and  @endDate " +
                " group by community ) " );
        line1_column4.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(line1_column4);

        //line1_column5
        Sql line1_column5 = Sqls.fetchInt("select count(*) from (select community from shequ.sqfw_form_summary_new where type in ('平安建设宣传')  and county = '观山街道' " +
                "and fill_date between  @startDate and  @endDate " +
                "group by community ) " );
        line1_column5.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(line1_column5);

        //消防安全部门表格 巡查记录
        Integer line2_column3 =sqfwFormsummaryNewService.count(Cnd.where(Sqfw_formSummary_new::getType,"=","安全排查整治")
                .and(Sqfw_formSummary_new::getCounty,"=","观山街道")
                .and(Sqfw_formSummary_new::getFillDate,"between",new Object[]{startDate, endDate}));

        //消防安全部门表格 台账
        Sql line2_column4 = Sqls.fetchInt("select count(*) from (select community from shequ.sqfw_form_summary_new where type in ('安全排查整治') and county = '观山街道' " +
                "and fill_date between  @startDate and  @endDate " +
                "group by community ) " );
        line2_column4.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(line2_column4);

        //综合治理中心 工作日志
        Integer line3_column2 =line1_column2;

        //综合治理中心 巡查记录
        Integer line3_column3 =sqfwFormsummaryNewService.count(Cnd.where(Sqfw_formSummary_new::getType,"=","社会治安")
                .and(Sqfw_formSummary_new::getCounty,"=","观山街道")
                .and(Sqfw_formSummary_new::getFillDate,"between",new Object[]{startDate, endDate}));

        //综合治理中心 台账
        //关爱人员探访
        Sql line3_column4_tanfang = Sqls.fetchInt("select count(*) from (select community from shequ.sqfw_form_summary_new where type in ('关爱人员探访') and county = '观山街道' " +
                "and fill_date between  @startDate and  @endDate " +
                "group by community ) " );
        line3_column4_tanfang.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(line3_column4_tanfang);
        //社会治安
        Sql line3_column4_zhian = Sqls.fetchInt("select count(*) from (select community from shequ.sqfw_form_summary_new where type in ('社会治安') and county = '观山街道' " +
                "and fill_date between  @startDate and  @endDate " +
                "group by community ) " );
        line3_column4_zhian.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(line3_column4_zhian);
        Integer line3_column4=line3_column4_tanfang.getInt()+line3_column4_zhian.getInt();
        //综合治理中心 台账
        Integer line3_column5=line1_column5.getInt();

        //食品安全部门 巡查记录
        Integer line4_column3=sqfwFormsummaryNewService.count(Cnd.where(Sqfw_formSummary_new::getType,"=","安全排查整治")
                .and(Sqfw_formSummary_new::getCounty,"=","观山街道")
                .and(Cnd.exps("param_data->>'fileOutputType'","like","%8%").or("param_data->>'fileOutputType'","like","%9%")));

        //食品安全部门 台账
        Sql line4_column4 = Sqls.fetchInt("select count(*) from ( select   community  from shequ.sqfw_form_summary_new " +
                " where county = '观山街道' and type='安全排查整治'  and (param_data->>'fileOutputType' like '%8%' or param_data->>'fileOutputType' like '%9%') " +
                " and fill_date between  @startDate and  @endDate" +
                " group by community)");
        line4_column4.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(line4_column4);

        //食品安全部门 台账
        Integer caiji_denominator = sqfwFormsummaryNewService.count(Cnd.where(Sqfw_formSummary_new::getType,"in",Arrays.asList("安全排查整治","社会治安","平安建设宣传","关爱人员探访"))
                .and(Sqfw_formSummary_new::getCounty,"=","观山街道")
                .and(Sqfw_formSummary_new::getFillDate,"between",new Object[]{startDate, endDate}));


        //减事 社区小助
        Sql xiaozhu = Sqls.create("select to_char(fill_date, 'YYYY-MM-DD') fill_date , count(*) from shequ.sqfw_form_summary_new where" +
                " type in ('社会治安','安全排查整治','平安建设宣传','关爱人员探访') and county = '观山街道'  and fill_date between  @startDate and  @endDate" +
                " group by to_char(fill_date, 'YYYY-MM-DD') order by fill_date");
        xiaozhu.setCallback(Sqls.callback.maps());
        xiaozhu.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(xiaozhu);
        Long xiaozuNum= 0L;

        List<NutMap> xiaozhulist= xiaozhu.getList(NutMap.class);
        for (NutMap nutMap : xiaozhulist) {
            xiaozuNum=xiaozuNum+nutMap.getLong("count");
        }
        //减事 传统
        Sql chuantong = Sqls.create("select fill_date,sum(count) as count  from " +
                "( select  fill_date,case when  type='社会治安' then count*3 when  type='安全排查整治' then count*6 when  type='平安建设宣传' then count*2 when  type='关爱人员探访' then count*4 end as count from " +
                "( select to_char(fill_date, 'YYYY-MM-DD') fill_date , count(*), type from shequ.sqfw_form_summary_new where type in ('社会治安','安全排查整治','平安建设宣传','关爱人员探访') and county = '观山街道' " +
                "and fill_date between   @startDate and  @endDate group by to_char(fill_date,'YYYY-MM-DD') ,type order by fill_date )) group by fill_date");
        chuantong.setCallback(Sqls.callback.maps());
        chuantong.params().set("startDate",startDate).set("endDate",endDate);
        dao.execute(chuantong);
        Long chuatongNum= 0l;
//        List<NutMap> chuatonglist= chuantong.getList(NutMap.class);
//        for (NutMap nutMap : chuatonglist) {
//            String countStr= (String) nutMap.get("count");
//            chuatongNum=chuatongNum+Long.valueOf(countStr);
//        }
        //商铺
        Integer shop =  shopSql.getInt();
        //房屋
        Integer tenement =  tenementSql.getInt();
        //法人
        Integer legalPerson =  legalPersonSql.getInt();
        //消防
        Integer fireFighting =  fireFightingSql.getInt();
        //食品
        Integer food =  foodSql.getInt();


        //应急管理部门表格
        NutMap line1 = NutMap.NEW().addv("column1", NumberUtil.add(shop,tenement,legalPerson))
                .addv("column2", line1_column2)
                .addv("column3", line1_column3)
                .addv("column4", line1_column4.getInt())
                .addv("column5", line1_column5.getInt())
                .addv("column6", NumberUtil.add(line1_column2,line1_column3,line1_column4.getInt(),line1_column5.getInt()))
                .addv("column7", NumberUtil.sub(NumberUtil.add(line1_column2,line1_column3,line1_column4.getInt(),line1_column5.getInt()),line1_column4.getInt(),line1_column5.getInt()));
        //消防安全部门表格
        NutMap line2 = NutMap.NEW().addv("column1",NumberUtil.add(shop,tenement,legalPerson,fireFighting))
                .addv("column2", "-")
                .addv("column3", line2_column3)
                .addv("column4", line2_column4.getInt())
                .addv("column5", "-")
                .addv("column6", NumberUtil.add(0,line2_column3,line2_column4.getInt()))
                .addv("column7", NumberUtil.sub(NumberUtil.add(0,line2_column3,line2_column4.getInt()),line2_column4.getInt()));

        //综合治理中心
        NutMap line3 = NutMap.NEW().addv("column1",NumberUtil.add(shop,tenement))
                .addv("column2", line3_column2)
                .addv("column3", line3_column3)
                .addv("column4", line3_column4)
                .addv("column5", line3_column5)
                .addv("column6", NumberUtil.add(line3_column2,line3_column3,line3_column4,line3_column5))
                .addv("column7", NumberUtil.sub(NumberUtil.add(line3_column2,line3_column3,line3_column4,line3_column5),line3_column4,line3_column5));

        //食品安全部门
        NutMap line4 = NutMap.NEW().addv("column1",NumberUtil.add(shop,food))
                .addv("column2", "-")
                .addv("column3", line4_column3)
                .addv("column4", line4_column4.getInt())
                .addv("column5", "-")
                .addv("column6", NumberUtil.add(line3_column2,line4_column3,line4_column4.getInt()))
                .addv("column7", NumberUtil.sub(NumberUtil.add(line3_column2,line4_column3,line4_column4.getInt()),line4_column4.getInt()));


        //共计
        NutMap line5 = NutMap.NEW()
                .addv("column2", NumberUtil.add(line1_column2,line3_column2))
                .addv("column3", NumberUtil.add(line1_column3,line2_column3,line3_column2))
                .addv("column4", NumberUtil.add(line1_column4.getInt(),line2_column4.getInt(),line3_column4,line4_column4.getInt()))
                .addv("column5", NumberUtil.add(line1_column5.getInt(),line3_column5,0));

        //总结
        NutMap line6 = NutMap.NEW()
                .addv("sumUp", NumberUtil.add((BigDecimal) line5.get("column2"),(BigDecimal) line5.get("column3"),(BigDecimal) line5.get("column4"),(BigDecimal) line5.get("column5")))
                .addv("reduceDuplication", shop*4+tenement*3+legalPerson*2)
                .addv("rate",NumberUtil.mul(NumberUtil.round(NumberUtil.div( shop*4+tenement*3+legalPerson*2,NumberUtil.add((BigDecimal) line1.get("column1"),(BigDecimal) line2.get("column1"),(BigDecimal) line3.get("column1"),(BigDecimal) line4.get("column1"))),4),100))
                .addv("share",line1_column3)
                .addv("shareRate",NumberUtil.mul(NumberUtil.round(NumberUtil.div(line1_column3,caiji_denominator),4),100));

        //采集
        NutMap caiji = NutMap.NEW().addv("shop",shop )
                .addv("tenement", tenement)
                .addv("legalPerson", legalPerson)
                .addv("fireFighting", fireFighting)
                .addv("food", food)
                .addv("sumUp", shop+tenement+legalPerson+fireFighting+food)
                .addv("fill", shop+tenement+legalPerson)
                .addv("rate", NumberUtil.mul(NumberUtil.round(NumberUtil.div(shop+tenement+legalPerson,shop+tenement+legalPerson+fireFighting+food),4),100)   );


        //归集
        NutMap guiji = NutMap.NEW().addv("line1",line1 )
                .addv("line2", line2)
                .addv("line3", line3)
                .addv("line4", line4)
                .addv("line5", line5)
                .addv("line6", line6);


        //减事
        NutMap jianshi = NutMap.NEW().addv("xiaozhu",xiaozhu.getList(NutMap.class) )
                .addv("chuantong",chuantong.getList(NutMap.class));



        //减时
        NutMap jianshi_01 = NutMap.NEW().addv("xiaozhu",Arrays.asList(NumberUtil.round( xiaozuNum*0.5,1),
                        NumberUtil.round( xiaozuNum*0.2,1),
                        NumberUtil.round(xiaozuNum*0.5,1),
                        NumberUtil.round(xiaozuNum*2,1),
                        NumberUtil.round(xiaozuNum*0.02,1),
                        NumberUtil.round(xiaozuNum*0.02,1),
                        NumberUtil.round(xiaozuNum*0.02,1)))
                .addv("chuantong",Arrays.asList(NumberUtil.round( xiaozuNum*3,1),
                        NumberUtil.round( xiaozuNum*1,1),
                        NumberUtil.round(xiaozuNum*2,1),
                        NumberUtil.round(xiaozuNum*4,1),
                        NumberUtil.round(xiaozuNum*1.5,1),
                        NumberUtil.round(xiaozuNum*2,1),
                        NumberUtil.round(xiaozuNum*2,1)));


        NutMap data = NutMap.NEW().addv("caiji", caiji).addv("guiji", guiji).addv("jianshi", jianshi).addv("jianshi_01",jianshi_01);
        return Result.data(data);
    }



}
